package com.parlons;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.Date;


/**
 * Created by wangyifan on 16/1/2.
 */


public class DatabaseServices extends SQLiteOpenHelper {

    private static DatabaseServices mInstance = null;
    public static String DATABASE_NAME = "parlons";
    public static int DATABASE_VERSION = 1;

    public static DatabaseServices getInstance(Context activityContext) {

        // Get the application context from the activityContext to prevent leak
        if (mInstance == null) {
            mInstance = new DatabaseServices(activityContext.getApplicationContext());
        }
        return mInstance;
    }

    private DatabaseServices(Context applicationContext) {
        super(applicationContext, DATABASE_NAME, null, DATABASE_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL("create table if not exists friend_list("
                + "id integer primary key,"
                + "avatar int,"
                + "username varchar,"
                + "friend_username varchar,"
                + "friend_name varchar)");

        db.execSQL("create table if not exists chat_list("
                + "id int primary key,"
                + "username varchar,"
                + "friend_username varchar,"
                + "last_message text,"
                + "unread_number int,"
                + "last_update_date varchar)");
/** from and to are user names */
        db.execSQL("create table if not exists message_list("
                + "id int primary key,"
                + "fromUsername varchar,"
                + "toUsername varchar,"
                + "content text,"
                + "created_date varchar,"
                + "username varchar)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    public void addFriend(String username, String friendUsername, String friendName) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into friend_list(username,friend_username, friend_name, avatar) values('" + username + "','" + friendUsername + "','" + friendName + "'," + R.drawable.avatar_1 + ")");
    }

    public void deleteFriend(String username, String friendUsername) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("delete FROM friend_list WHERE username = '" + username + "', AND friend_username = '" + friendUsername + "'");
    }

    public boolean friendExists(String username, String friendUsername) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("friend_list", null, "friend_username = ? AND username = ?", new String[]{friendUsername, username}, null, null, "id asc");
        boolean result = (cursor.getCount() != 0);
        cursor.close();
        System.out.println("friend exists?" + result + friendUsername + "  " + "  " + username);
        return result;
    }

    public Cursor getFriendsCursor(String username) {
        try {
            SQLiteDatabase db = getWritableDatabase();
            Cursor cursor = db.rawQuery("SELECT id as _id, friend_username, friend_name FROM friend_list WHERE username = '" + username + "'", null);
            return cursor;
        } catch (Exception e) {
            System.out.println(e);
        }

        return null;
    }


    public void addMessage(String fromUsername, String toUsername, String content, String username) {
        System.out.println("add message");
        SQLiteDatabase db = getWritableDatabase();
        System.out.println("get db");
        db.execSQL("insert into message_list(fromUsername,toUsername, content, username ) values('" + fromUsername + "','" + toUsername + "','" + content + "', '" + username + "')");
        System.out.println("add message from " + fromUsername + " to :" + toUsername + " : " + content);
    }

    public void deleteMessage(String username, String friendUsername) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("delete FROM friend_list WHERE username = '" + username + "', AND friend_username = '" + friendUsername + "'");
        ;
        System.out.println("delete friend" + friendUsername);
    }

    public boolean messageExists(String username, String friendUsername) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("friend_list", null, "friend_username = ? AND username = ?", new String[]{friendUsername, username}, null, null, "id asc");
        boolean result = (cursor.getCount() != 0);
        cursor.close();
        System.out.println("message exists?" + result);
        return result;
    }

    public Cursor getMessageCursor(String fromUsername, String toUsername, String username) {
        try {
            SQLiteDatabase db = getWritableDatabase();
            Cursor cursor = db.rawQuery("SELECT id as _id, fromUsername as fromUser, toUsername as toUser, content FROM message_list WHERE ((fromUsername = '" + fromUsername + "' AND toUsername = '" + toUsername + "') OR ( fromUsername = '" + toUsername + "' AND toUsername = '" + fromUsername + "')) AND ( username = '" + username + "')", null);
            return cursor;
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

    public void addOrUpdateChat(String username, String friendUsername, String lastMessage) {
        System.out.println("add chat: user " + username + " with " + friendUsername);
        Date date = new Date(System.currentTimeMillis());

        boolean chatExists = chatExists(username, friendUsername);
        SQLiteDatabase db = getWritableDatabase();
        if (!chatExists) {
            System.out.println("~~~~add chat");
            db.execSQL("insert into chat_list (username, friend_username, last_message, unread_number, last_update_date ) values( '" + username + "', '" + friendUsername + "', '" + lastMessage + "'," + 0 + ", '" + date.toString() + "' )");
            System.out.println("~~~~add chat ok");
        } else {
            db.execSQL("UPDATE chat_list SET last_message = '" + lastMessage + "', last_update_date = '" + date.toString() + "' WHERE username = '" + username + "' AND friend_username = '" + friendUsername + "' ");
            System.out.println("~~~~update chat");
        }
    }

    public void addUnread(String username, String friendUsername) {
        boolean chatExists = chatExists(username, friendUsername);
        SQLiteDatabase db = getWritableDatabase();
        if (chatExists) {
            Cursor cursor = db.query("chat_list", new String[]{"unread_number"}, "friend_username = ? AND username = ?", new String[]{friendUsername, username}, null, null, "id asc");
            int iUnread = cursor.getColumnIndex("unread_number");
            cursor.moveToFirst();
            int unreadNumber = cursor.getInt(iUnread);
            unreadNumber++;
            db.execSQL("UPDATE chat_list SET unread_number = " + unreadNumber + " WHERE username = '" + username + "' AND friend_username = '" + friendUsername + "' ");
        }
    }

    public void resetUnread(String username, String friendUsername) {
        boolean chatExists = chatExists(username, friendUsername);
        SQLiteDatabase db = getWritableDatabase();
        if (chatExists) {
            db.execSQL("UPDATE chat_list SET unread_number = " + 0 + " WHERE username = '" + username + "' AND friend_username = '" + friendUsername + "' ");
            System.out.println("~~~~reset unread with :" + friendUsername);
        }
    }

    public long getTotalUnread(String username) {
        SQLiteDatabase db = getWritableDatabase();
        long total;
        try {
            Cursor cursor = db.query("chat_list", new String[]{"SUM(unread_number)"}, "username = ?", new String[]{username}, null, null, "id asc");
            cursor.moveToFirst();
            total = cursor.getLong(0);
            System.out.println("total unread: " + total);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }

        return total;
    }

    public void deleteChat(String username, String friendUsername) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("delete FROM chat_list WHERE username = '" + username + "' AND friend_username = '" + friendUsername + "'");
        System.out.println("delete chat with " + friendUsername);
    }

    public boolean chatExists(String username, String friendUsername) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query("chat_list", null, "friend_username = ? AND username = ?", new String[]{friendUsername, username}, null, null, "id asc");
        boolean result = (cursor.getCount() != 0);
        cursor.close();
        System.out.println("chat exists?" + result);
        return result;
    }

    public Cursor getChatCursor(String username) {
        try {
            SQLiteDatabase db = getWritableDatabase();
            Cursor cursor = db.rawQuery("SELECT id as _id, friend_username, last_message, unread_number FROM chat_list WHERE username = '" + username + "' ORDER BY last_update_date DESC", null);
            return cursor;
        } catch (Exception e) {
            System.out.println(e);
        }
        return null;
    }

}

